package model;

import java.math.BigDecimal;

import javax.servlet.http.HttpServletRequest;

public class Student extends BaseModel{

	private Integer id;
	private String sName;
	private Integer sSex;
	private String sGrade;
	private Integer cId;
	private Integer mId;
	private String clazz;
	private String empCompany;
	private String empCity;
	private String empStation;
	private BigDecimal salary;
	private Integer comType;

	private String sex;
	private String comtype;
	private String cName;
	private String mName;
	
  	//业务相关变量
  	private Integer pageNo;//页码，第几页
  	private Integer rowCount;//单页行数
	public Student() {
		
	}

	public String getComtype() {
		return comtype;
	}

	public void setComtype(String comtype) {
		this.comtype = comtype;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getsName() {
		return sName;
	}

	public void setsName(String sName) {
		this.sName = sName;
	}

	public Integer getsSex() {
		return sSex;
	}

	public void setsSex(Integer sSex) {
		this.sSex = sSex;
	}

	public String getsGrade() {
		return sGrade;
	}

	public void setsGrade(String sGrade) {
		this.sGrade = sGrade;
	}

	public Integer getcId() {
		return cId;
	}

	public void setcId(Integer cId) {
		this.cId = cId;
	}

	public Integer getmId() {
		return mId;
	}

	public void setmId(Integer mId) {
		this.mId = mId;
	}

	public String getClazz() {
		return clazz;
	}

	public void setClazz(String clazz) {
		this.clazz = clazz;
	}

	public String getEmpCompany() {
		return empCompany;
	}

	public void setEmpCompany(String empCompany) {
		this.empCompany = empCompany;
	}

	public String getEmpCity() {
		return empCity;
	}

	public void setEmpCity(String empCity) {
		this.empCity = empCity;
	}

	public String getEmpStation() {
		return empStation;
	}

	public void setEmpStation(String empStation) {
		this.empStation = empStation;
	}

	public BigDecimal getSalary() {
		return salary;
	}

	public void setSalary(BigDecimal salary) {
		this.salary = salary;
	}

	public Integer getComType() {
		return comType;
	}

	public void setComType(Integer comType) {
		this.comType = comType;
	}

	public String getcName() {
		return cName;
	}

	public void setcName(String cName) {
		this.cName = cName;
	}

	public String getmName() {
		return mName;
	}

	public void setmName(String mName) {
		this.mName = mName;
	}
	
	public Integer getPageNo() {
		return pageNo;
	}

	public void setPageNo(Integer pageNo) {
		this.pageNo = pageNo;
	}

	public Integer getRowCount() {
		return rowCount;
	}

	public void setRowCount(Integer rowCount) {
		this.rowCount = rowCount;
	}

	//封装工作单元，将请求中的参数赋值给对应的变量
	public void getParams(HttpServletRequest req) {
		this.id           = getIValueINM(req.getParameter("id"));
		this.sName        = getValueINM(req.getParameter("sName"));
		this.sSex         = getIValueINM(req.getParameter("sSex"));
		this.sGrade       = getValueINM(req.getParameter("sGrade"));
		this.cId          = getIValueINM(req.getParameter("cId"));
		this.mId          = getIValueINM(req.getParameter("mId"));
		this.clazz        = getValueINM(req.getParameter("clazz")); 
		this.empCompany   = getValueINM(req.getParameter("empCompany"));   
		this.empCity      = getValueINM(req.getParameter("empCity"));    
		this.empStation   = getValueINM(req.getParameter("empStation"));   
		this.salary       = getDValueINM(req.getParameter("salary"));  
		this.comType      = getIValueINM(req.getParameter("comType"));
		
		this.pageNo       = dealPageNo(req.getParameter("pageNo"));
		this.rowCount     = dealRowCount(req.getParameter("rowCount"));
	}

	//组装插入sql语句
	public String getInsertSql() {
		//拼接values
		StringBuffer values = new StringBuffer();
		
		//拼接columns
		StringBuffer sql = new StringBuffer("insert into student(");
		
		if (id != null) {
			sql.append("id,");
			values.append(id + ",");
		}
		if (sName != null) {
			sql.append("s_name,");
			values.append("'").append(sName).append("',");
		}
		if (sSex != null) {
			sql.append("s_sex,");
			values.append(sSex + ",");
		}
		if (sGrade != null) {
			sql.append("s_grade,");
			values.append("'").append(sGrade).append("',");
		}
		if (cId != null) {
			sql.append("c_id,");
			values.append(cId + ",");
		}
		if (mId != null) {
			sql.append("m_id,");
			values.append(mId + ",");
		}
		if (clazz != null) {
			sql.append("clazz,");
			values.append("'").append(clazz).append("',");
		}
		if (empCompany != null) {
			sql.append("emp_company,");
			values.append("'").append(empCompany).append("',");
		}
		if (comType != null) {
			sql.append("com_type,");
			values.append(comType + ",");
		}
		if (empCity != null) {
			sql.append("emp_city,");
			values.append("'").append(empCity).append("',");
		}
		if (empStation != null) {
			sql.append("emp_station,");
			values.append("'").append(empStation).append("',");
		}
		if (salary != null) {
			sql.append("salary,");
			values.append(salary + ",");
		}
		//删除字段拼接的最后一个逗号
		sql.deleteCharAt(sql.length()-1);
		//删除值拼接的最后一个逗号
		values.deleteCharAt(values.length()-1);
		
		sql.append(")values(").append(values).append(")");
		
		return sql.toString();
	}
	//组装查询sql
	public String getSelectSql() {
//		StringBuffer sql = new StringBuffer("select s.*, c.college_name, m.m_name from student as s ");
//		sql.append(" left join college as c on s.c_id = c.id");
//		sql.append(" left join major as m on s.m_id = m.id where 1=1");
		StringBuffer sql = new StringBuffer("select * from stu_view where 1=1");
		
		if (id != null) {
			sql.append(" and id = ").append(id);
		}
		if (sName != null) {
			sql.append(" and s_name = '").append(sName).append("'");
		}
		if (sGrade != null) {
			sql.append(" and s_grade = '").append(sGrade).append("'");
		}
		if (cId != null) {
			sql.append(" and c_id = ").append(cId);
		}
		if (mId != null) {
			sql.append(" and m_id = ").append(mId);
		}
		return sql.toString();
	}
	
	//组装查询总行数sql
	public String getTotalCountSql() {
		StringBuffer sql = new StringBuffer("select count(*) from student where 1=1 ");
		if (sName != null) {
			sql.append(" and s_name = '").append(sName).append("'");
		}
		if (sGrade != null) {
			sql.append(" and s_grade = '").append(sGrade).append("'");
		}
		if (cId != null) {
			sql.append(" and c_id = ").append(cId);
		}
		if (mId != null) {
			sql.append(" and m_id = ").append(mId);
		}
		return sql.toString();
	}
	//组装更新sql
	public String getUpdateSql() {
		//拼接values
		StringBuffer values = new StringBuffer();
		
		//拼接columns
		StringBuffer sql = new StringBuffer("update student ");
		
		if (sName != null) {
			values.append(" s_name = '").append(sName).append("',");
		}
		if (sSex != null) {
			values.append(" s_sex = ").append(sSex).append(",");
		}
		if (sGrade != null) {
			values.append(" s_grade = '").append(sGrade).append("',");
		}
		if (cId != null) {
			values.append(" c_id = ").append(cId).append(",");
		}
		if (mId != null) {
			values.append(" m_id = ").append(mId).append(",");
		}
		if (clazz != null) {
			values.append(" clazz = '").append(clazz).append("',");
		}
		if (empCompany != null) {
			values.append(" emp_company = '").append(empCompany).append("',");
		}
		if (comType != null) {
			values.append(" com_type = ").append(comType).append(",");
		}
		if (empCity != null) {
			values.append(" emp_city = '").append(empCity).append("',");
		}
		if (empStation != null) {
			values.append(" emp_station = '").append(empStation).append("',");
		}
		if (salary != null) {
			values.append(" salary = ").append(salary).append(",");
		}
		if (values.length() > 0 ) {
			//删除值拼接的最后一个逗号
			values.deleteCharAt(values.length()-1);
			
			sql.append(" set ").append(values).append(" where id = ").append(id);
			return sql.toString();
		}else {
			return null;
		}

	}
}
